using System;
using System.Collections.Generic;
using System.Text;
using Fieldbook.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using Fieldbook.Common;

namespace Fieldbook.DAL
{
    public static class FieldMapDAO
    {
        
        #region insert
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSQL = @"INSERT INTO dbo.FieldMap (POSTALCODE , DESCRIPTION, MAPFILENAME) 
                    VALUES (@POSTALCODE , @DESCRIPTION, @MAPFILENAME) SET @FIELDMAPID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@FIELDMAPID", DbType.Int32, 4);
            db.AddInParameter(insertCommand, "@POSTALCODE", DbType.String);
            db.AddInParameter(insertCommand, "@DESCRIPTION", DbType.String);
            db.AddInParameter(insertCommand, "@MAPFILENAME", DbType.Guid);
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, FieldMap entity)
        {
            db.SetParameterValue(cmd, "@POSTALCODE", entity.PostalCode);
            db.SetParameterValue(cmd, "@DESCRIPTION", entity.Description);
            db.SetParameterValue(cmd, "@MAPFILENAME", entity.MapFileName);
        }
        public static bool InsertFieldMap(FieldMap entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.FieldMapID = (int?)db.GetParameterValue(cmd, "@FIELDMAPID");
            }
            return res;
        }
        #endregion

        #region select
        private static List<FieldMap> LoadFieldMaps(SqlDatabase db, DbCommand command)
        {
            List<FieldMap> res = new List<FieldMap>();
            IDataReader reader = null;
            try
            {
                reader = db.ExecuteReader(command);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        FieldMap entity = new FieldMap();
                        entity.FieldMapID = reader.GetInt32(0);
                        entity.PostalCode = reader.GetString(1);
                        entity.Description = reader.IsDBNull(2) ? null : reader.GetString(2);
                        entity.MapFileName = reader.GetGuid(3);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<FieldMap> LoadFieldMaps()
        {
            string selectSQL = @"SELECT FIELDMAPID, POSTALCODE , DESCRIPTION, MAPFILENAME FROM FieldMap";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            return LoadFieldMaps(db, cmd);
        }

        public static List<FieldMap> LoadHouseHoldFieldMaps(long userID)
        {
            string selectSQL = @"SELECT F.FIELDMAPID, F.POSTALCODE , F.DESCRIPTION, F.MAPFILENAME FROM HouseHold HH 
INNER JOIN AppUser AU ON HH.MEMBERID = AU.USERID 
INNER JOIN UserMap UM ON UM.USERID = HH.HEADID
INNER JOIN FieldMap F on F.FIELDMAPID = UM.FIELDMAPID
WHERE AU.USERID = @USERID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@USERID", DbType.Int64, userID);
            return LoadFieldMaps(db, cmd);
        }

        public static List<FieldMap> LoadFreeFieldMaps(long userID)
        {
            string selectSQL = @"SELECT F.FIELDMAPID, F.POSTALCODE , F.DESCRIPTION, F.MAPFILENAME 
FROM HouseHold HH 
INNER JOIN AppUser AU ON HH.MEMBERID = AU.USERID 
LEFT OUTER JOIN UserMap UM ON UM.USERID != HH.HEADID
RIGHT OUTER JOIN FieldMap F ON UM.FIELDMAPID = F.FIELDMAPID 
WHERE AU.USERID = @USERID OR UM.USERID IS NULL";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@USERID", DbType.Int64, userID);
            return LoadFieldMaps(db, cmd);
        }

        public static FieldMap GetFieldMap(int fieldMapId)
        {
            string selectSQL = @"SELECT FIELDMAPID, POSTALCODE , DESCRIPTION, MAPFILENAME FROM FieldMap WHERE FIELDMAPID = @FIELDMAPID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@FIELDMAPID", DbType.Int32, fieldMapId);
            return LoadFieldMaps(db, cmd)[0];
        }
        #endregion
    }
}
